package com.aplikata.evok.service;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.myfaces.custom.fileupload.UploadedFile;
import org.primefaces.model.DefaultTreeNode;
import org.primefaces.model.SortOrder;
import org.primefaces.model.TreeNode;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.aplikata.bo.Node;
import com.aplikata.bo.Role;
import com.aplikata.bo.User;
import com.aplikata.constants.Constants;
import com.aplikata.constants.ConstantsMsg;
import com.aplikata.dao.NodeDao;
import com.aplikata.evok.bo.DoctorSchedule;
import com.aplikata.evok.bo.EvokService;
import com.aplikata.evok.bo.Patient;
import com.aplikata.evok.bo.PatientTherapy;
import com.aplikata.evok.bo.PatientTherapyAttachment;
import com.aplikata.evok.bo.PatientTherapyComment;
import com.aplikata.evok.bo.PatientTherapyNode;
import com.aplikata.evok.bo.Therapy;
import com.aplikata.evok.constants.EvokConstants;
import com.aplikata.evok.dao.DoctorScheduleDao;
import com.aplikata.evok.dao.PatientTherapyDao;
import com.aplikata.exception.CustomException;
import com.aplikata.service.PublicServiceImpl;
import com.aplikata.util.FacesUtil;
import com.aplikata.util.FileUtil;
import com.aplikata.util.StringUtil;
import com.aplikata.util.YunDateUtil;

/**
 * @ClassName: EvokTherapyServiceImpl
 * @Description: TODO
 * @author Huyun
 * @date May 24, 2012 1:04:36 PM
 */
@Service("evokTherapyService")
public class EvokTherapyServiceImpl extends PublicServiceImpl implements EvokTherapyService {

	@Autowired
	private PatientTherapyDao patientTherapyDao;

	@Autowired
	private NodeDao nodeDao;

	@Autowired
	private DoctorScheduleDao doctorScheduleDao;

	public EvokTherapyServiceImpl() {
	}

	/*
	 * Title: getTherapyNodeTree Description:
	 * @return
	 * @see com.aplikata.evok.service.EvokTherapyService#getTherapyNodeTree()
	 */
	@Override
	public TreeNode getTherapyNodeTree() {
		return getTherapyNodeTree(null, null);
	}

	@SuppressWarnings("unchecked")
	@Override
	public TreeNode getTherapyTreeForDoctor(Long roleId, PatientTherapy pt) {
		if (roleId == null || pt == null)
			return null;

		TreeNode rootMenu = new DefaultTreeNode("Root", null);
		String sql = "SELECT {et.*}, ep.therapy_id is not null as checked "
				+ " FROM evk_role_therapy e, evk_therapy et "
				+ " LEFT JOIN evk_patient_therapy_category ep ON et.id=ep.therapy_id "
				+ " AND ep.patient_therapy_id = " + pt.getId() + " WHERE e.therapy_id=et.id AND e.role_id= " + roleId
				+ " ORDER BY et.parent_id IS NOT NULL, et.level, et.sort ";

		List<Object[]> list = this.getPublicDao().getSQLQuery(sql).addEntity("et", Therapy.class)
				.addScalar("checked", new org.hibernate.type.BooleanType()).list();

		if (list == null || list.isEmpty())
			return rootMenu;

		Map<Long, TreeNode> treemap = new HashMap<Long, TreeNode>();

		for (Object[] obj : list) {
			boolean checked = Boolean.valueOf(obj[1].toString());
			Therapy th = (Therapy) obj[0];
			if (th.getParent() == null) {
				TreeNode tn = new DefaultTreeNode(th, rootMenu);
				tn.setSelected(checked);
				treemap.put(th.getId(), tn);
			} else {
				TreeNode temp = new DefaultTreeNode(th, treemap.get(th.getParent().getId()));
				temp.setSelected(checked);
				treemap.put(th.getId(), temp);
			}
		}

		return rootMenu;
	}

	@SuppressWarnings({ "unchecked", "rawtypes" })
	@Override
	public TreeNode getTherapyNodeTree(Integer status, Long roleId) {
		String hql = " from Therapy t where t.parent.id is null ";
		if (status != null) {
			hql += " and t.status = " + status;
		}
		hql += " order by t.sort ";

		List roleTherapyList = null;
		if (!FacesUtil.isKeyNull(roleId)) {
			roleTherapyList = getPublicDao().findByNamedQuery(Therapy.QY_FIND_BY_ROLE, new Object[] { roleId });
		}

		List<Therapy> firstList = (List<Therapy>) getPublicDao().find(hql);

		TreeNode rootMenu = new DefaultTreeNode("Root", null);
		if (firstList == null || firstList.isEmpty())
			return rootMenu;

		for (Therapy therapy : firstList) {
			TreeNode tn = new DefaultTreeNode(therapy, rootMenu);
			tn.setSelected(false);
			if (roleTherapyList != null && roleTherapyList.contains(therapy)) {
				tn.setSelected(true);
			}

			this.addNodes(tn, therapy, status, roleTherapyList);
		}

		return rootMenu;
	}

	@SuppressWarnings({ "unchecked", "rawtypes" })
	private void addNodes(TreeNode node, Therapy therapy, Integer status, List roleTherapyList) {
		if (therapy == null)
			return;

		String hql = " from Therapy d where d.parent.id = " + therapy.getId();
		if (status != null) {
			hql += " and d.status = " + status;
		}
		hql += " order by d.sort";

		List<Therapy> children = getPublicDao().find(hql);
		if (children == null || children.isEmpty()) {
			return;
		}

		for (Therapy tr : children) {
			TreeNode tmp = new DefaultTreeNode(tr, node);

			tmp.setSelected(false);
			if (roleTherapyList != null && roleTherapyList.contains(tr)) {
				tmp.setSelected(true);
			}

			addNodes(tmp, tr, status, roleTherapyList);
		}
	}

	/*
	 * Title: updateTherapy Description:
	 * @param therapy
	 * @return
	 * @throws Exception
	 * @see
	 * com.aplikata.evok.service.EvokTherapyService#updateTherapy(com.aplikata
	 * .evok.bo.Therapy)
	 */
	@SuppressWarnings("unchecked")
	@Override
	public Therapy updateTherapy(Therapy therapy) throws Exception {
		if (therapy == null) {
			return therapy;
		}

		if (StringUtil.isStrEmpty(therapy.getName())) {
			throw new CustomException(ConstantsMsg.MSG_REQUIRE, new Object[] { FacesUtil.getMessage("default_name") });
		}

		if (therapy.getStatus() == null) {
			therapy.setStatus(Constants.COMMON_STATUS_NORMAL);
		}

		Therapy parent = therapy.getParent();

		if (parent == null || FacesUtil.isKeyNull(parent.getId())) {
			therapy.setParent(null);
			therapy.setLevel(1);
		} else if (parent.getId().equals(therapy.getId())) {
			therapy.setParent(null);
			therapy.setLevel(1);
		} else {
			therapy.setLevel(parent.getLevel() + 1);
		}

		return (Therapy) getPublicDao().merge(therapy);
	}

	/**
	 * @Title: stopTherapy
	 * @Description: TODO
	 * @param @param therapy
	 * @param @return
	 * @param @throws Exception
	 * @return Therapy
	 * @throws
	 */
	@SuppressWarnings("unchecked")
	@Override
	public Therapy stopTherapy(Therapy therapy) throws Exception {
		if (therapy == null)
			return therapy;

		therapy.setStatus(Constants.COMMON_STATUS_STOP);
		therapy = (Therapy) getPublicDao().merge(therapy);

		// change children status to be stopped.
		String sql = " update evk_therapy t set t.status = " + Constants.COMMON_STATUS_STOP + " where t.parent_id = "
				+ therapy.getId();
		getPublicDao().updateBySql(sql);
		return therapy;
	}

	@Override
	public void updateRoleTherapy(Role role, TreeNode[] selectedTherapies) throws Exception {
		if (role == null || FacesUtil.isKeyNull(role.getId()))
			return;

		String sql1 = " delete from evk_role_therapy where role_id = " + role.getId();
		getPublicDao().updateBySql(sql1);
		String temp = "";
		TreeNode parent = null;

		for (int i = 0; i < selectedTherapies.length; i++) {
			TreeNode treenode = selectedTherapies[i];
			TreeNode parentnode = treenode.getParent();

			if (parentnode != null && !parentnode.equals(parent) && !parentnode.isSelected()) {
				parent = parentnode;
				if (parentnode.getData() != null && parentnode.getData() instanceof Therapy) {
					temp += "(" + role.getId() + "," + ((Therapy) parentnode.getData()).getId() + "),";
				}
			}

			Therapy therapy = (Therapy) treenode.getData();
			temp += "(" + role.getId() + "," + therapy.getId() + "),";
		}
		if (temp.endsWith(",")) {
			temp = temp.substring(0, temp.lastIndexOf(","));
		}
		if (!StringUtil.isStrEmpty(temp)) {
			String sql2 = " insert into evk_role_therapy (role_id, therapy_id) values " + temp;
			getPublicDao().updateBySql(sql2);
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public EvokService updateService(EvokService service) throws Exception {
		if (service == null) {
			return service;
		}

		if (service.getDomain() == null) {
			throw new CustomException(ConstantsMsg.MSG_REQUIRE, new Object[] { "Domain" });
		}

		if (StringUtil.isStrEmpty(service.getName())) {
			throw new CustomException(ConstantsMsg.MSG_REQUIRE, new Object[] { FacesUtil.getMessage("default_name") });
		}

		String hql = " from EvokService s where s.domain.id = " + FacesUtil.getDomainFromSession().getId()
				+ " and s.name = '" + service.getName() + "'";

		if (!FacesUtil.isKeyNull(service.getId())) {
			hql += " and s.id != " + service.getId();
		}

		EvokService obj = (EvokService) getPublicDao().getFirstRow(hql);
		if (obj != null) {
			throw new CustomException(ConstantsMsg.ERROR_EXIST, new Object[] { service.getName() });
		}

		return (EvokService) getPublicDao().merge(service);
	}

	@SuppressWarnings("unchecked")
	@Override
	public PatientTherapy updatePatientTherapy(PatientTherapy pt, DoctorSchedule schedule,
			TreeNode[] selectedTherapyNodes, List<PatientTherapyNode> pnList) throws Exception {

		Long tempid = pt.getId();

		if (selectedTherapyNodes == null || selectedTherapyNodes.length == 0) {
			throw new CustomException(ConstantsMsg.MSG_REQUIRE,
					new Object[] { FacesUtil.getMessage(Therapy.LB_CATEGORY) });
		}

		if (patientTherapyDao.validate(pt)) {
			if (FacesUtil.isKeyNull(pt.getId())) {
				pt.setCreateTime(YunDateUtil.getDateTime());
				pt.setCreator(FacesUtil.getUserFromSession());
				pt.setOperTime(YunDateUtil.getDateTime());
			}
		}

		// if(EvokConstants.PATIENT_THERAPY_TYPE_PERIOD == pt.getType()){
		// if(FacesUtil.isKeyNull(tempid)){
		// UserEvent event = new UserEvent(FacesUtil.getUserFromSession());
		// event.setTimeFrom(pt.getStartDate());
		// event.setTimeTo(pt.getEndDate());
		// event.setType(EvokConstants.USER_EVENT_THERAPY);
		// getPublicDao().merge(event);
		// }
		// }

		pt = patientTherapyDao.merge(pt);

		String nodestr = "";
		String deletesql = " delete from evk_patient_therapy_category where patient_therapy_id = " + pt.getId();

		getPublicDao().updateBySql(deletesql);

		for (TreeNode node : selectedTherapyNodes) {
			nodestr += "(" + ((Therapy) node.getData()).getId() + "," + pt.getId() + "),";
		}

		if (nodestr.endsWith(",")) {
			nodestr = nodestr.substring(0, nodestr.lastIndexOf(","));
		}

		if (!StringUtil.isStrEmpty(nodestr)) {
			String insertsql = " insert into evk_patient_therapy_category values " + nodestr;
			getPublicDao().updateBySql(insertsql);
		}

		if (!FacesUtil.isKeyNull(tempid)) {
			if (pnList == null || pnList.isEmpty())
				return pt;

			String deletenodesql = " delete from evk_patient_therapy_node where patient_therapy_id = " + pt.getId();
			getPublicDao().updateBySql(deletenodesql);
			String insertnodesql = " insert into evk_patient_therapy_node (patient_therapy_id, node_id, value) values ";
			String str = "";

			for (PatientTherapyNode pn : pnList) {
				Node node = pn.getNode();
				String value = pn.getValue();

				if (nodeDao.validateDynamicNode(node, value)) {
					if (!StringUtil.isStrEmpty(value)) {
						str += "(" + pt.getId() + "," + node.getId() + ",'" + value + "'),";
						// pn.setPatientTherapy(pt);
						// getPublicDao().merge(pn);
					}
				}
			}

			if (str.endsWith(",")) {
				str = str.substring(0, str.lastIndexOf(","));
				getPublicDao().updateBySql(insertnodesql + str);
			}
		}

		if (schedule != null) {
			// if (EvokConstants.DOC_RESERVATION_STATUS_NORMAL ==
			// schedule.getStatus()) {
			// schedule.setStatus(EvokConstants.DOC_RESERVATION_STATUS_FINISH);
			// }

			if (FacesUtil.isKeyNull(tempid)) {
				schedule.setPatientTherapy(pt);
			}

			if (schedule.getStatus() == null) {
				schedule.setStatus(EvokConstants.DOC_RESERVATION_STATUS_NORMAL);
			}

			if (schedule.getDoctor() == null) {
				schedule.setDoctor(FacesUtil.getUserFromSession());
			}

			if (schedule.getOper() == null) {
				schedule.setOper(FacesUtil.getUserFromSession());
			}

			schedule.setOperTime(YunDateUtil.getDateTime());
			doctorScheduleDao.validate(schedule);
			schedule = (DoctorSchedule) getPublicDao().merge(schedule);
		}

		return pt;
	}

	@SuppressWarnings("unchecked")
	@Override
	public DoctorSchedule updatePatientTherapySchedule(PatientTherapy pt, DoctorSchedule schedule,
			TreeNode[] selectedTherapyNodes, List<PatientTherapyNode> pnList) throws Exception {

		if (schedule == null)
			return null;

		Long tempid = pt.getId();

		if (selectedTherapyNodes == null || selectedTherapyNodes.length == 0) {
			throw new CustomException(ConstantsMsg.MSG_REQUIRE,
					new Object[] { FacesUtil.getMessage(Therapy.LB_CATEGORY) });
		}

		if (patientTherapyDao.validate(pt)) {
			if (FacesUtil.isKeyNull(pt.getId())) {
				pt.setCreateTime(YunDateUtil.getDateTime());
				pt.setCreator(FacesUtil.getUserFromSession());
				pt.setOperTime(YunDateUtil.getDateTime());
			}
		}

		pt = patientTherapyDao.merge(pt);

		String nodestr = "";
		String deletesql = " delete from evk_patient_therapy_category where patient_therapy_id = " + pt.getId();

		getPublicDao().updateBySql(deletesql);

		for (TreeNode node : selectedTherapyNodes) {
			nodestr += "(" + ((Therapy) node.getData()).getId() + "," + pt.getId() + "),";
		}

		if (nodestr.endsWith(",")) {
			nodestr = nodestr.substring(0, nodestr.lastIndexOf(","));
		}

		if (!StringUtil.isStrEmpty(nodestr)) {
			String insertsql = " insert into evk_patient_therapy_category values " + nodestr;
			getPublicDao().updateBySql(insertsql);
		}

		if (!FacesUtil.isKeyNull(tempid)) {
			if (pnList != null && !pnList.isEmpty()) {
				String deletenodesql = " delete from evk_patient_therapy_node where patient_therapy_id = " + pt.getId();
				getPublicDao().updateBySql(deletenodesql);
				String insertnodesql = " insert into evk_patient_therapy_node (patient_therapy_id, node_id, value) values ";
				String str = "";

				for (PatientTherapyNode pn : pnList) {
					Node node = pn.getNode();
					String value = pn.getValue();

					if (nodeDao.validateDynamicNode(node, value)) {
						if (!StringUtil.isStrEmpty(value)) {
							str += "(" + pt.getId() + "," + node.getId() + ",'" + value + "'),";
							// pn.setPatientTherapy(pt);
							// getPublicDao().merge(pn);
						}
					}
				}

				if (str.endsWith(",")) {
					str = str.substring(0, str.lastIndexOf(","));
					getPublicDao().updateBySql(insertnodesql + str);
				}
			}
		}

		if (schedule != null) {
			if (FacesUtil.isKeyNull(tempid)) {
				schedule.setPatientTherapy(pt);
			}

			if (schedule.getStatus() == null) {
				schedule.setStatus(EvokConstants.DOC_RESERVATION_STATUS_NORMAL);
			}

			if (schedule.getDoctor() == null) {
				schedule.setDoctor(FacesUtil.getUserFromSession());
			}

			if (schedule.getOper() == null) {
				schedule.setOper(FacesUtil.getUserFromSession());
			}

			schedule.setOperTime(YunDateUtil.getDateTime());
			doctorScheduleDao.validate(schedule);
			schedule = (DoctorSchedule) getPublicDao().merge(schedule);
		}

		return schedule;
	}

	@SuppressWarnings("unchecked")
	@Override
	public PatientTherapyAttachment updateTherapyAttachment(UploadedFile therapyAtt, PatientTherapyAttachment att)
			throws Exception {
		if (FacesUtil.isKeyNull(att.getId()) && therapyAtt == null) {
			throw new CustomException(ConstantsMsg.MSG_REQUIRE, new Object[] { FacesUtil.getMessage("default_upload") });
		}
		if (therapyAtt != null) {
			String attname = "";
			attname = FileUtil.getFileName(therapyAtt.getName().replace(' ', '_'));
			attname = FileUtil.utf8ToLatin(attname);
			attname = attname.substring(attname.lastIndexOf("\\") + 1, attname.length());
			if (attname.length() > PatientTherapyAttachment.FILENAME_LENGTH) {
				attname = attname.substring(0, attname.length() - 1);
			}

			att.setAttName(attname);

			attname = "dm" + FacesUtil.getDomainFromSession().getId().toString() + "-"
					+ att.getPatientTherapy().getId() + "-" + YunDateUtil.formatDateTime("yyyyMMddHHmmss") + attname;
			String filename = FacesUtil.getServletContext().getRealPath(
					(new StringBuilder("/upload\\therapy\\")).append(attname).toString());
			att.setPath(FacesUtil.getServletContext().getContextPath() + "/upload/therapy/" + attname);
			FileUtil.writeToFile(therapyAtt.getInputStream(), filename);
		}

		att = (PatientTherapyAttachment) this.getPublicDao().merge(att);
		return att;
	}

	@SuppressWarnings("unchecked")
	@Override
	public PatientTherapyComment updateComment(PatientTherapyComment comment) throws Exception {
		if (comment.getMessage().isEmpty())
			throw new CustomException(FacesUtil.getMessage("comment_message_empty"));
		
		comment.setSender(FacesUtil.getUserFromSession());
		String roleName = FacesUtil.getUserFromSession().getRole().getName();
		if(roleName.startsWith("doc-")){
			String sql = "SELECT u.* FROM evk_patient_therapy e, evk_domain_patient dp, evk_patient p, _user u" 
					+ " WHERE e.domain_patient_id = dp.id and dp.patient_id = p.id and p.user_id = u.id"
					+ " AND e.id = " + comment.getPatientTherapy().getId();
			List<User> list = getPublicDao().getSQLQuery(sql).addEntity("u", User.class).list();
			comment.setReceiver(list.get(0));
		}else{
			comment.setReceiver(comment.getPatientTherapy().getCreator());
		}
				
		comment.setOperTime(YunDateUtil.getDateTime());
		comment.setReadStatus(false);
		comment.setSendEmail(true);
		comment = (PatientTherapyComment) merge(comment);
		return comment;
	}

	@Override
	public void submitCommentReadStatus(PatientTherapy pt) {
		if (!FacesUtil.isKeyNull(pt.getId())) {
			String sql = "update evk_patient_therapy_comment e set read_status = 1"
					+ " where read_status = 0 and patient_therapy_id = " + pt.getId() + " and receiver_id = "
					+ FacesUtil.getUserFromSession().getId();

			getPublicDao().updateBySql(sql);
		}
	}

	@SuppressWarnings({ "rawtypes", "unchecked" })
	@Override
	public Object[] getPatientTherapyArchive(Date startDateFrom, Date startDateTo, int first, int pageSize,
			String sortField, SortOrder sortOrder, Map<String, String> filters) {
		Object[] obj = new Object[2];
		String whereHql = "";
		if (FacesUtil.getUserFromSession().getRole().getName().equals(EvokConstants.ROLE_PATIENT)) {
			Patient patient = (Patient) findSingleByNamedQuery(Patient.QY_FIND_BY_USER,
					new Object[] { FacesUtil.getUserFromSession().getId() });
			whereHql = " where pt.domainPatient.patient.id = " + patient.getId();
		} else {
			whereHql = " where pt.creator.domain.id = " + FacesUtil.getUserFromSession().getDomain().getId();
		}

		String hql = " select pt from PatientTherapy pt";
		String counthql = " select count(pt.id) from PatientTherapy pt";

		if (startDateFrom != null) {
			whereHql += " and pt.startDate >= '" + YunDateUtil.formatDate(startDateFrom) + "'";
		}

		if (startDateTo != null) {
			whereHql += " and pt.startDate <= '" + YunDateUtil.formatDate(startDateTo) + "'";
		}

		hql += whereHql;
		counthql += whereHql;

		if (!StringUtil.isStrEmpty(sortField)) {
			sortField = "pt." + sortField;
		} else {
			sortField = "pt.startDate";
			sortOrder = SortOrder.DESCENDING;
		}

		Map<String, String> filterFunctions = new HashMap<String, String>();
		Map<String, String> dataType = new HashMap<String, String>();
		Map<String, String> newfilters = new HashMap<String, String>();

		if (!filters.isEmpty()) {
			filterFunctions = new HashMap<String, String>();
			dataType = new HashMap<String, String>();

			Iterator it = filters.entrySet().iterator();
			while (it.hasNext()) {
				Map.Entry pairs = (Map.Entry) it.next();
				String filterProperty = (String) pairs.getKey();
				String filterValue = filters.get(filterProperty);

				if (PatientTherapy.PATIENT_NAME.equals(filterProperty)
						|| PatientTherapy.PATIENT_SURNAME.equals(filterProperty)
						|| PatientTherapy.DOCTOR_NAME.equals(filterProperty)
						|| PatientTherapy.DOCTOR_SURNAME.equals(filterProperty)) {
					newfilters.put("pt." + filterProperty, filterValue);
					filterFunctions.put("pt." + filterProperty, Constants.FILTER_CONSTAINS);
					dataType.put("pt." + filterProperty, Constants.FILTER_DT_STRING);
				} else if (PatientTherapy.START_DATE.equals(filterProperty)
						|| PatientTherapy.END_DATE.equals(filterProperty)) {
					newfilters.put("pt." + filterProperty, filterValue);
					filterFunctions.put("pt." + filterProperty, Constants.FILTER_EXACT);
					dataType.put("pt." + filterProperty, Constants.FILTER_DT_STRING);
				} else if (PatientTherapy.STATUS.equals(filterProperty)) {
					newfilters.put("pt." + filterProperty, filterValue);
					filterFunctions.put("pt." + filterProperty, Constants.FILTER_EXACT);
					dataType.put("pt." + filterProperty, Constants.FILTER_DT_INTEGER);
				}
			}
		}

		List<?> list = getPublicDao().find(hql, first, pageSize, sortField, sortOrder, newfilters, dataType,
				filterFunctions);
		int count = getPublicDao().findCount(counthql, newfilters, dataType, filterFunctions);
		obj[0] = count;
		obj[1] = list;

		return obj;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<PatientTherapyAttachment> getAttList(PatientTherapy patientTherapy) {
		String hql = " from PatientTherapyAttachment pa where pa.patientTherapy.id = " + patientTherapy.getId()
				+ " order by pa.sort";
		return (List<PatientTherapyAttachment>) this.find(hql);
	}

	@Override
	public String getPriceTotal(PatientTherapy patientTherapy) {
		String priceSql = "select concat(sum(price_total), ' ', p.name)"
				+ " from evk_therapy_service ts, evk_service s, _parameter p, _parameter pc"
				+ " where ts.service_id=s.id and s.currency=p.value and p.parent_id=pc.id"
				+ " and pc.name='TherapyServiceCurrency' and patient_therapy_id= " + patientTherapy.getId();
		Object obj = findSingleBySql(priceSql);
		return obj == null ? " " : obj.toString();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<PatientTherapyNode> getPatientTherapyNodeList(Long roleId, PatientTherapy patientTherapy) {
		String sql = "SELECT {epn.*}, {p.*} FROM (select n.* from _access a, _node n where a.role_id = " + roleId
				+ " and a.node_id = n.id and n.type = " + EvokConstants.NODE_TYPE_THERAPY
				+ ") p	 left join evk_patient_therapy_node epn on p.id = epn.node_id "
				+ " and epn.patient_therapy_id = " + patientTherapy.getId() + " order by p.sort";

		List<Object> list = getSqlQuery(sql).addEntity("epn", PatientTherapyNode.class).addEntity("p", Node.class)
				.list();

		List<PatientTherapyNode> pnList = new ArrayList<PatientTherapyNode>();
		if (list == null || list.isEmpty())
			return pnList;

		for (int i = 0; i < list.size(); i++) {
			Object[] obj = (Object[]) list.get(i);
			PatientTherapyNode pn = (PatientTherapyNode) obj[0];
			Node node = (Node) obj[1];

			if (pn != null) {
				pn.setNode(node);
				pnList.add(pn);
			} else {
				pnList.add(new PatientTherapyNode(node, patientTherapy));
			}
		}

		return pnList;
	}
	
}
